﻿Imports System.IO

Imports System.Windows.Forms
Imports Microsoft.Office.Interop.Excel
Imports System.Web.UI.WebControls



Public Class XLSXCell
    Public Property CellName As String
    Public Property CellValue As String
    Public Property CellColumnIndex As Integer
    Public Property CellRowIndex As Integer


    Public Sub New(ByVal _CellName As String, ByVal _CellValue As String)
        CellName = _CellName
        CellValue = _CellValue
        Dim RowIndex As String = StringFunc.Get_NumberFromString(CellName)
        If RowIndex = "" Then
            CellRowIndex = 1
        Else
            CellColumnIndex = RetrieveColumnIndex(CellName.Substring(0, CellName.Length - RowIndex.Length))
            CellRowIndex = RowIndex
        End If

    End Sub

    Public Function RetrieveColumnIndex(ByVal CellName As String) As String
        Dim result As String = ""
        Dim CurrentLetterIndex As Integer = 0
        CurrentLetterIndex = 26 * (CellName.Length - 1)
        CellName = CellName.ToUpper

        Dim i As Integer = (AscW(CellName.Substring(CellName.Length - 1, 1)) - AscW("A") + 1) + CurrentLetterIndex
        Return i
    End Function

End Class

Public Class XLSXHelper
    Enum ExportFileType
        XLSX = 1
        PDF = 2
        CSV = 3
        XLS = 4
    End Enum

    Public Shared Function ExportListViewToXLSX(ByVal lv As ListView, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                                   ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String,
                                   Optional ByVal ExcelTemplateFile As String = "",
                                    Optional ByVal CustomSheetName As String = "",
                                    Optional ByVal AddCellsByRef As List(Of XLSXCell) = Nothing,
                                    Optional ByVal StartingRow As Integer = 0) As Boolean
        Dim result As Boolean

        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim MissingValue As Object = System.Reflection.Missing.Value

        If StartingRow < 1 Then
            StartingRow = 1
        End If

        ExcelApp = New Microsoft.Office.Interop.Excel.Application


        If File.Exists(ExcelTemplateFile) Then
            ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
            ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If

        If ExcelTemplateFile.Trim.Length > 0 Then

        End If
        ExcelWorkSheet = ExcelWorkBook.Sheets(1)

        If CustomSheetName.Trim.Length > 0 Then
            ExcelWorkSheet.Name = CustomSheetName
        End If



        Dim NoOfColumns As Integer = lv.Columns.Count
        Dim NoOfRows As Integer = lv.Items.Count

        If ToIncludeHeader Then
            NoOfRows = NoOfRows + 1
            For c = 0 To lv.Columns.Count - 1
                ExcelWorkSheet.Cells(StartingRow, c + 1) = _
                    lv.Columns(c).Text
            Next

            For r = 0 To lv.Items.Count - 1
                For c = 0 To lv.Columns.Count - 1
                    If Not lv.Items(r).SubItems(c).Text Is Nothing Then
                        ExcelWorkSheet.Cells(r + StartingRow + 1, c + 1) = _
                            lv.Items(r).SubItems(c).Text
                    End If

                Next
            Next
        Else
            For r = 0 To lv.Items.Count - 1
                For c = 0 To lv.Columns.Count - 1
                    If Not lv.Items(r).SubItems(c).Text Is DBNull.Value Then
                        ExcelWorkSheet.Cells(r + StartingRow, c + 1) = _
                           lv.Items(r).SubItems(c).Text
                    End If

                Next
            Next
        End If

        'Add extra cell if any
        AddCustomCells(ExcelWorkSheet, AddCellsByRef)

        Dim CellRange As Microsoft.Office.Interop.Excel.Range

        CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows, NoOfColumns))
        CellRange.EntireColumn.AutoFit()
        'Apply border to selected cells
        If IncludeBorder Then
            ApplyCellBorder(CellRange)
        End If
        'Save work book
        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)


        ExcelWorkBook.Close(False)
        ExcelApp.Quit()

        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)
        ReleaseExcelObject(ExcelWorkSheet)
        Return result
    End Function


    Public Shared Function ExportDataTableToXLSX(ByVal tbl As System.Data.DataTable, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                                     ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String,
                                     Optional ByVal ExcelTemplateFile As String = "", Optional ByVal UseTableNameAsSheetName As Boolean = False,
                                    Optional ByVal AddCellsByRef As List(Of XLSXCell) = Nothing,
                                    Optional ByVal StartingRow As Integer = 1) As Boolean
        Dim result As Boolean

        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim MissingValue As Object = System.Reflection.Missing.Value

        If StartingRow < 1 Then
            StartingRow = 1
        End If

        ExcelApp = New Microsoft.Office.Interop.Excel.Application


        If File.Exists(ExcelTemplateFile) Then
            ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
            ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If

        If ExcelTemplateFile.Trim.Length > 0 Then

        End If
        ExcelWorkSheet = ExcelWorkBook.Sheets(1)


        If UseTableNameAsSheetName Then
            ExcelWorkSheet.Name = tbl.TableName
        End If



        Dim NoOfColumns As Integer = tbl.Columns.Count
        Dim NoOfRows As Integer = tbl.Rows.Count

        'Add extra cell if any
        AddCustomCells(ExcelWorkSheet, AddCellsByRef)

        If ToIncludeHeader Then
            NoOfRows = NoOfRows + 1
            For c = 0 To tbl.Columns.Count - 1
                ExcelWorkSheet.Cells(StartingRow, c + 1) = _
                    tbl.Columns(c).ColumnName
            Next

            For r = 0 To tbl.Rows.Count - 1
                For c = 0 To tbl.Columns.Count - 1
                    If Not tbl.Rows(r)(c) Is DBNull.Value Then
                        ExcelWorkSheet.Cells(r + StartingRow + 1, c + 1) = _
                            tbl.Rows(r)(c)
                    End If

                Next
            Next
        Else
            For r = 0 To tbl.Rows.Count - 1
                For c = 0 To tbl.Columns.Count - 1
                    If Not tbl.Rows(r)(c) Is DBNull.Value Then
                        ExcelWorkSheet.Cells(r + StartingRow, c + 1) = _
                            tbl.Rows(r)(c)
                    End If

                Next
            Next
        End If

        Dim CellRange As Microsoft.Office.Interop.Excel.Range

        CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows, NoOfColumns))
        CellRange.EntireColumn.AutoFit()

        'Apply border to selected cells
        If IncludeBorder Then
            ApplyCellBorder(CellRange)
        End If
        'Save work book
        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)

        ExcelWorkBook.Close(False)
        ExcelApp.Quit()
        ReleaseExcelObject(CellRange)
        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)
        ReleaseExcelObject(ExcelWorkSheet)
        Return result
    End Function

    Private Shared Sub ApplyCellBorder(ByVal CellRange As Microsoft.Office.Interop.Excel.Range)
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous
        CellRange.Borders(Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical).LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous

    End Sub
    Private Shared Sub AddCustomCells(ByVal ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet, ByVal AddCellsByRef As List(Of XLSXCell))
        If AddCellsByRef IsNot Nothing Then
            For i As Integer = 0 To AddCellsByRef.Count - 1
                ExcelWorkSheet.Cells(AddCellsByRef.Item(i).CellRowIndex, AddCellsByRef.Item(i).CellColumnIndex) = AddCellsByRef.Item(i).CellValue
            Next

        End If
    End Sub

    Private Shared Sub SaveWorkBook(ByVal ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook, ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String)
        Dim DestinationFileName As String = GenerateDestinationFileName(FilePath, FileName, FileType)
        If (FileType = ExportFileType.PDF) Then
            ExcelWorkBook.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, DestinationFileName, AcceptRejectRule.Cascade, False, False, System.Reflection.Missing.Value, System.Reflection.Missing.Value, False, System.Reflection.Missing.Value)
        ElseIf (FileType = ExportFileType.CSV) Then
            ExcelWorkBook.ExportAsFixedFormat(XlFileFormat.xlCSV, DestinationFileName)
        Else
            'ExcelWorkSheet.SaveAs()
            ExcelWorkBook.SaveAs(DestinationFileName)

        End If

    End Sub
    Private Shared Function GenerateDestinationFileName(ByVal FilePath As String, ByVal FileName As String, ByVal FileType As ExportFileType) As String
        FilePath = AddSlash(FilePath)
        If Not System.IO.Directory.Exists(FilePath) Then
            System.IO.Directory.CreateDirectory(FilePath)
        End If

        Dim DestinationFileName As String = FilePath
        If FileName.EndsWith(".pdf") Or FileName.EndsWith(".xlsx") Or FileName.EndsWith(".csv") Or FileName.EndsWith(".xls") Then
            DestinationFileName = DestinationFileName & FileName
        Else
            If (FileType = ExportFileType.PDF) Then
                DestinationFileName = DestinationFileName & FileName & ".pdf"
            ElseIf (FileType = ExportFileType.CSV) Then
                DestinationFileName = DestinationFileName & FileName & ".csv"
            ElseIf (FileType = ExportFileType.XLS) Then
                DestinationFileName = DestinationFileName & FileName & ".xls"
            Else
                DestinationFileName = DestinationFileName & FileName & ".xlsx"
            End If
        End If

        Return DestinationFileName

    End Function
    Public Shared Function ExportGridViewToXLSX(ByVal dgv As GridView, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                                    ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String,
                                    Optional ByVal ExcelTemplateFile As String = "", Optional ByVal CustomSheetName As String = "",
                                    Optional ByVal AddCellsByRef As List(Of XLSXCell) = Nothing,
                                    Optional ByVal StartingRow As Integer = 1) As Boolean
        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim MissingValue As Object = System.Reflection.Missing.Value

        If StartingRow < 1 Then
            StartingRow = 1
        End If

        Dim DirIndex As Integer = 0
        DirIndex = FileName.LastIndexOf("\")
        Dim DirName As String = FileName.Substring(0, DirIndex + 1)
        If Not System.IO.Directory.Exists(DirName) Then
            System.IO.Directory.CreateDirectory(DirName)
        End If

        ExcelApp = New Microsoft.Office.Interop.Excel.Application


        If File.Exists(ExcelTemplateFile) Then
            ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
            ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If

        ExcelWorkSheet = ExcelWorkBook.Sheets(1)


        If CustomSheetName.Trim.Length > 0 Then
            ExcelWorkSheet.Name = CustomSheetName
        End If

        Dim NoOfColumns As Integer = dgv.Columns.Count
        Dim NoOfRows As Integer = dgv.Rows.Count

        If ToIncludeHeader Then
            NoOfRows = NoOfRows + 1
            For c = 0 To dgv.Rows.Count - 1
                ExcelWorkSheet.Cells(StartingRow, c + 1) = _
                dgv.Columns(c).HeaderText
            Next
            For r = 0 To dgv.Rows.Count - 2
                For c = 0 To dgv.Columns.Count - 1
                    ExcelWorkSheet.Cells(r + StartingRow + 1, c + 1) = _
                        dgv.Rows(r).Cells(c).ToString
                Next
            Next
        Else
            For r = 0 To dgv.Rows.Count - 2
                For c = 0 To dgv.Columns.Count - 1
                    ExcelWorkSheet.Cells(r + StartingRow, c + 1) = _
                        dgv.Rows(r).Cells(c).ToString
                Next
            Next
        End If
        'Add extra cell if any
        AddCustomCells(ExcelWorkSheet, AddCellsByRef)


        Dim CellRange As Microsoft.Office.Interop.Excel.Range
        CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows, NoOfColumns))
        CellRange.EntireColumn.AutoFit()

        If IncludeBorder Then
            ApplyCellBorder(CellRange)
        End If

        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)

        ExcelWorkBook.Close(False)
        ExcelApp.Quit()

        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)
        ReleaseExcelObject(ExcelWorkSheet)
        Return True

    End Function
    Public Shared Function ExportDataGridViewToXLSX(ByVal dgv As DataGridView, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                                    ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String, Optional ByVal ExcelTemplateFile As String = "",
                                    Optional ByVal CustomSheetName As String = "",
                                    Optional ByVal AddCellsByRef As List(Of XLSXCell) = Nothing,
                                    Optional ByVal StartingRow As Integer = 1
                                    ) As Boolean
        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

        Dim MissingValue As Object = System.Reflection.Missing.Value

        Dim DirIndex As Integer = 0
        DirIndex = FileName.LastIndexOf("\")
        Dim DirName As String = FileName.Substring(0, DirIndex + 1)
        If Not System.IO.Directory.Exists(DirName) Then
            System.IO.Directory.CreateDirectory(DirName)
        End If

        If StartingRow < 1 Then
            StartingRow = 1
        End If

        ExcelApp = New Microsoft.Office.Interop.Excel.Application

        If File.Exists(ExcelTemplateFile) Then
            ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
            ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If

        ExcelWorkSheet = ExcelWorkBook.Sheets(1)



        If CustomSheetName.Trim.Length > 0 Then
            ExcelWorkSheet.Name = CustomSheetName
        End If

        Dim NoOfColumns As Integer = dgv.Columns.Count
        Dim NoOfRows As Integer = dgv.Rows.Count

        If ToIncludeHeader Then
            NoOfRows = NoOfRows + StartingRow
            For c = 0 To dgv.ColumnCount - 1
                ExcelWorkSheet.Cells(StartingRow, c + 1) = _
                dgv.Columns(c).HeaderText
            Next

            For r = 0 To dgv.RowCount - 2
                For c = 0 To dgv.ColumnCount - 1
                    ExcelWorkSheet.Cells(r + StartingRow + 1, c + 1) = _
                        dgv(c, r).Value.ToString()
                Next
            Next
        Else
            For r = 0 To dgv.RowCount - 2
                For c = 0 To dgv.ColumnCount - 1
                    ExcelWorkSheet.Cells(r + StartingRow, c + 1) = _
                        dgv(c, r).Value.ToString()
                Next
            Next
        End If

        'Add extra cell if any
        AddCustomCells(ExcelWorkSheet, AddCellsByRef)


        Dim CellRange As Microsoft.Office.Interop.Excel.Range

        CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows, NoOfColumns))
        CellRange.EntireColumn.AutoFit()

        'Apply border to selected cells
        If IncludeBorder Then
            ApplyCellBorder(CellRange)
        End If
        'Save work book
        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)

        ExcelWorkBook.Close(False)
        ExcelApp.Quit()

        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)
        ReleaseExcelObject(ExcelWorkSheet)
        Return True
    End Function




    Public Shared Function ExportDataSetToXLSX(ByVal ds As System.Data.DataSet, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                                  ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String, Optional ByVal ExcelTemplateFile As String = "", Optional ByVal NoOfHeaderColumnsToSkip As Integer = 0,
                                  Optional ByVal UseTableNameAsSheetName As Boolean = False) As Boolean
        Dim result As Boolean


        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim MissingValue As Object = System.Reflection.Missing.Value

        ExcelApp = New Microsoft.Office.Interop.Excel.Application

        If File.Exists(ExcelTemplateFile) Then
            ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
            ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If




        Dim DestinationFileName As String = GenerateDestinationFileName(FilePath, FileName, FileType)
        Dim CellRange As Microsoft.Office.Interop.Excel.Range
        Dim NoOfTable As Int32 = 1
        For Each tbl As Data.DataTable In ds.Tables




            ExcelWorkSheet = ExcelWorkBook.Sheets(NoOfTable)


            If UseTableNameAsSheetName Then
                ExcelWorkSheet.Name = tbl.TableName
            End If

            Dim NoOfColumns As Integer = tbl.Columns.Count
            Dim NoOfRows As Integer = tbl.Rows.Count

            If ToIncludeHeader Then
                NoOfRows = NoOfRows + 1
                For c = 0 To tbl.Columns.Count - 1
                    ExcelWorkSheet.Cells(1, c + 1) = _
                        tbl.Columns(c).ColumnName
                Next

                For r = 0 To tbl.Rows.Count - 1
                    For c = 0 To tbl.Columns.Count - 1
                        If Not tbl.Rows(r)(c) Is DBNull.Value Then
                            ExcelWorkSheet.Cells(r + 2, c + 1) = _
                                tbl.Rows(r)(c)
                        End If

                    Next
                Next
            Else
                For r = 0 To tbl.Rows.Count - 1
                    For c = 0 To tbl.Columns.Count - 1
                        If Not tbl.Rows(r)(c) Is DBNull.Value Then
                            ExcelWorkSheet.Cells(r + 1 + NoOfHeaderColumnsToSkip, c + 1) = _
                                tbl.Rows(r)(c)
                        End If

                    Next
                Next
            End If


            CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows + 1, NoOfColumns))
            CellRange.EntireColumn.AutoFit()
            'Apply border to selected cells
            If IncludeBorder Then
                ApplyCellBorder(CellRange)
            End If

            NoOfTable = NoOfTable + 1


            ReleaseExcelObject(CellRange)
            ReleaseExcelObject(ExcelWorkSheet)

        Next

        'Save work book
        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)



        ExcelWorkBook.Close(False)
        ExcelApp.Quit()
        ExcelApp.Workbooks.Close()

        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)

        Return result
    End Function



    Private Shared Sub ReleaseExcelObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

    Private Shared Function AddSlash(ByVal FilePath As String) As String
        If FilePath.Substring(FilePath.Length - 1) <> "\" Then
            FilePath = FilePath & "\"
        End If

        Return FilePath
    End Function

End Class
